package com.otc.model;

import java.util.ArrayList;
import com.otc.dbhandler.*;

public class OpinionThatCounts {
	OpinionThatCountsUser user;

	/*
	 * Method : loginValidate Return : integer Argument : String,String
	 * Responsibility : To validate a user login.
	 */
	public int loginValidate(String username, String password) {
		int temp = 0;
		String query = "select count(*) as total from login where username='"
				+ username + "' and password='" + password + "'";
		DBHandler db = new DBHandler();
		db.getConnection();
		String count = db.executeSelect(query);
		temp = Integer.parseInt(count);
		db.closeConnection();
		return temp;
	}

	public int returnMovieCount() {
		int temp = 0;
		String query = "select count(*) as total from movie ";
		DBHandler db = new DBHandler();
		db.getConnection();
		String count = db.executeSelect(query);
		temp = Integer.parseInt(count);
		db.closeConnection();
		return temp;
	}

	public int retrieveCommentCount(String id) {
		int temp = 0;
		String query = "select count(*) from comment where movie_id='" + id
				+ "' ";
		DBHandler db = new DBHandler();
		db.getConnection();
		String count = db.executeSelect(query);
		temp = Integer.parseInt(count);
		db.closeConnection();
		return temp;
	}

	/*
	 * public ArrayList<String> returnMovieData() { DBHandler db = new
	 * DBHandler(); db.getConnection();
	 * 
	 * String query = "select movie_name from movie order by movie_time desc";
	 * ArrayList<String> res = db.retrieveConvId(query);
	 * 
	 * db.closeConnection(); return res; }
	 */

	/*
	 * Method : view Bollywood Movie Details Return : ArrayList<Movies> Argument
	 * : String Movie ID Responsibility: To view Bollywood Movies Details
	 */
	public ArrayList<Movies> retrieveBollywoodMovies() {

		String query = "SELECT movie_id,"
				+ " movie_name,"
				+ " movie_image,"
				+ " user_id,"
				+ " substr(CAST(movie_description AS CHAR(10000) CHARACTER SET utf8),1,200) as movie_description,"
				+ " movie_like," + "movie.movie_dislike," + " movie_rating,"
				+ "movie.movie_time," + " user_id" + " movie_category_id" + " FROM movie"
				+ " order by movie_time desc";
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<Movies> movie = db.retrieveMovieDetails(query);

		db.closeConnection();

		return movie;

	}
	
	public ArrayList<String> retrieveMovieDetailForComments(String id) {

		String query = "SELECT movie_name,"
				+ " movie_image,"
				+ " user_id,"
				+ " substr(CAST(movie_description AS CHAR(10000) CHARACTER SET utf8),1,8000) as movie_description,"
				+ " movie_like," + "movie_dislike," + " movie_rating,"
				+ "movie_time FROM movie " + " WHERE movie_id = "
				+ "'" + id + "'";
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<String> movie = db.retrieveMovieDataForComments(query);

		db.closeConnection();

		return movie;

	}

	
	
	

	/*
	 * Method : retrieveUserName : String Movie ID Responsibility: To retrieve
	 * username
	 */
	public ArrayList<String> retrieveUserName() {

		String query = "select luid.user_name,mv.movie_name from login_user_id luid,movie mv where mv.user_id=luid.user_id order by mv.movie_time desc";
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<String> userName = db.retrieveUserName(query);

		db.closeConnection();

		return userName;
	}
	
	public ArrayList<String> retrieveUserNameForComment(String id) {

		String query = "select user_name from login_user_id where user_id="
				+ "'" + id + "'";
		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<String> userName = db.retrieveUserName(query);

		db.closeConnection();

		return userName;
	}
	
	

	public ArrayList<Comments> retrieveComments(String id) {
		String query = "SELECT comment_id," + " user_name_for_movie_comment,"
				+ " movie_id,"
				+ " comment_description,"
				+ " email_id," + " comment_date FROM comment " + " WHERE movie_id = "
				+ "'" + id + "'";

		DBHandler db = new DBHandler();
		db.getConnection();
		ArrayList<Comments> comment = db.retrieveComments(query);

		db.closeConnection();

		return comment;
	}

	public void insertMovieComment(String userNameForComment, int movieId, String commentDesc,
			String emailId,String comment_date) {
		String query = "insert into comment (user_name_for_movie_comment,movie_id,comment_description,email_id,comment_date) values('"
				+ userNameForComment
				+ "','"
				+ movieId
				+ "','"
				+ commentDesc
				+ "','"
				+ emailId + "','"
				+ comment_date + "')";

		DBHandler db = new DBHandler();

		db.getConnection();
		db.addDataToComments(query);
		db.closeConnection();

	}
}

/*
 * DBHandler db = new DBHandler(); db.getConnection(); ArrayList<String> conHist
 * = db.retrieveConvId(query); db.closeConnection(); return conHist; } } /*
 * 
 * Method : GetAccidentHistoryDetails Return : String[] Argument : String
 * Responsibility : To get Accident History Details corresponding to selected
 * proposalId.
 * 
 * public String[] getAccidentDetails(String pId) { String[] result = new
 * String[2]; String query = "select acc_hist_id from accident_history " +
 * "where proposal_id =" + "'" + pId + "'"; DBHandler db = new DBHandler();
 * db.getConnection(); String accId = db.executeSelect(query); result[0] =
 * accId; String query1 = "select count(*) from  accident_history ah, " +
 * "proposal p where p.proposal_id = ah.proposal_id " +
 * "and p.user_id = (select user_id from proposal " + "where proposal_id =" +
 * "'" + pId + "')"; String count = db.executeSelect(query1); result[1] = count;
 * return result;
 * 
 * 
 * }
 * 
 * Method : AddAccidentHistoryDetails Return : integer Argument : Accident
 * Responsibility : To add Accident History Details corresponding to selected
 * proposalId.
 * 
 * public int addAccident(Accident a) { String query1 =
 * "select comm_sequence.NextVal from dual"; DBHandler db = new DBHandler();
 * db.getConnection();
 * 
 * String accId = db.executeSelect(query1); accId = "acc_" + accId; String query
 * = "insert into  accident_history (" + "ACC_HIST_ID,PROPOSAL_ID," +
 * "DATE_OF_ACCIDENT,NATURE_OF_ACCIDENT,COMMENTS) " + "values ('" + accId +
 * "','" + a.getProposalId() + "','" + a.getDateOfAccident() + "','" +
 * a.getNatureOfAccident() + "','" + a.getComments() + "' )";
 * 
 * int res = db.executeDML(query); db.closeConnection(); return res; }
 * 
 * 
 * Method : deleteAccidentHistoryDetails Return : integer Argument : String[]
 * accidentId Responsibility : To delete Accident History Details corresponding
 * to selected accidentIds
 * 
 * public int deleteAccidentHistoryDetails(String[] accId) { String s = ""; for
 * (int i = 0; i < ((accId.length) - 1); i++) { s = s + "'" + accId[i] + "', ";
 * } s = s + "'" + accId[((accId.length) - 1)] + "'"; String query =
 * "delete from ACCIDENT_HISTORY where ACC_HIST_ID in" + "(" + s+ ")"; DBHandler
 * db = new DBHandler(); db.getConnection(); int i = db.executeDML(query);
 * db.closeConnection(); return i; }
 * 
 * 
 * Method : displayAccidentDetails (with the check box to delete the selected
 * accidentId details) Return : ArrayList<Accident> Argument : userId
 * Responsibility : To view Accident History Details corresponding to a
 * particular userId
 * 
 * public ArrayList<Accident> displayAccidentDetails(String userId) { DBHandler
 * db = new DBHandler(); db.getConnection(); String query; query =
 * "select user_id  from ACCIDENT_HISTORY ah, PROPOSAL p where " +
 * "ah.PROPOSAL_ID = p.PROpOSAL_ID and p.user_id =" + userId; String rs1 =
 * db.executeSelect(query); if (rs1 == null) { return null; } else { query =
 * "select '' as VEH_ID,ah.ACC_HIST_ID,ah.PROPOSAL_ID," +
 * "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT," +
 * "noa.NOA_TYPE as NATURE_OF_ACCIDENT,ah.COMMENTS " +
 * "from ACCIDENT_HISTORY ah, PROPOSAL p,NATURE_OF_ACCIDENT noa" +
 * " where ah.PROPOSAL_ID = p.PROPOSAL_ID " +
 * "and noa.NOA_ID=ah.NATURE_OF_ACCIDENT and p. USER_ID=" + userId; al =
 * db.executeView(query); return al; } }
 * 
 * 
 * Method : updateAccidentDetails(with the radio button to select the details to
 * be updated) Return : integer Argument : String accHistId, String
 * dateOfAccident, String natureOfAccident, String comments Responsibility: To
 * update Accident History Details corresponding to a particular
 * accidentHistoryId
 * 
 * public int updateAccident(String accHistId, String dateOfAccident, String
 * natureOfAccident, String comments) { String query =
 * "update accident_history set NATURE_OF_ACCIDENT='" + natureOfAccident + "', "
 * + "DATE_OF_ACCIDENT='" + dateOfAccident + "', COMMENTS='" + comments + "' " +
 * "where ACC_HIST_ID='" + accHistId + "'"; DBHandler db = new DBHandler();
 * db.getConnection(); int res = db.executeDML(query); db.closeConnection();
 * return res; }
 * 
 * 
 * Method : ViewAccident Return : ArrayList<Accident> Argument : String id
 * Responsibility : To view Accident History Detail corresponding to a
 * particular AccidentId
 * 
 * public ArrayList<Accident> viewAccident(String id) { String query =
 * "select ah.ACC_HIST_ID,ah.PROPOSAL_ID," +
 * "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT," +
 * "ah.NATURE_OF_ACCIDENT,ah.COMMENTS,v.veh_id " +
 * "from accident_history ah inner join" +
 * " proposal v on ah.proposal_id=v.proposal_id " + "where ah.acc_hist_id='" +
 * id + "' ";
 * 
 * DBHandler db = new DBHandler(); db.getConnection(); al =
 * db.executeView(query); db.closeConnection(); return al; }
 * 
 * 
 * Method : ViewAccidentDetails Return : Accident object Argument : accidentId
 * Responsibility : To view Accident History Detail corresponding to a
 * particular AccidentId
 * 
 * public ArrayList<Accident> viewAccidentDetails(String accId, String userId){
 * String accHisId = null, proposalId = null, doa = null, natureOfAcc = null,
 * comments = null; String query; if(accId.equals("all")) query =
 * "select ah.ACC_HIST_ID,ah.PROPOSAL_ID," +
 * "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT," +
 * "noa.NOA_TYPE as NATURE_OF_ACCIDENT,ah.COMMENTS " +
 * "from accident_history ah, NATURE_OF_ACCIDENT noa " +
 * "where ah.NATURE_OF_ACCIDENT=noa.NOA_ID " + "and proposal_id in " +
 * "(select proposal_id from proposal where user_id ="+ "'" + userId + "')";
 * 
 * else { query = " select ah.ACC_HIST_ID,ah.PROPOSAL_ID," +
 * "to_char(ah.DATE_OF_ACCIDENT)as DATE_OF_ACCIDENT," +
 * "noa.NOA_TYPE as NATURE_OF_ACCIDENT,ah.COMMENTS " +
 * "from accident_history ah, NATURE_OF_ACCIDENT noa " +
 * "where ah.NATURE_OF_ACCIDENT=noa.NOA_ID " + "and ACC_HIST_ID = '" + accId +
 * "'"; }
 * 
 * DBHandler db = new DBHandler(); db.getConnection(); ResultSet rs =
 * db.executeSelect1(query); try { while (rs.next()) { accHisId =
 * rs.getString(1); proposalId = rs.getString(2); doa = rs.getString(3);
 * natureOfAcc = rs.getString(4); comments = rs.getString(5); Accident a = new
 * Accident("", accHisId, proposalId, doa, natureOfAcc, comments); al.add(a); }
 * } catch (SQLException s) { s.printStackTrace(); } return al; }
 * 
 * 
 * Method : display AccidentHistoryId corresponding to a particular userId
 * Return : ArrayList<String> Argument : userId Responsibility: To view
 * AccidentHistoryId corresponding to a particular userId
 * 
 * 
 * 
 * public ArrayList<String> retrieveAccId(String userId) { String query =
 * "select  ACC_HIST_ID from ACCIDENT_HISTORY ah, PROPOSAL p where ah.PROPOSAL_ID = p.PROPOSAL_ID AND p.USER_ID='"
 * +userId+"'"; DBHandler db = new DBHandler(); db.getConnection();
 * ArrayList<String> accId = db.retrieveConvId(query); db.closeConnection();
 * return accId; }
 * 
 * 
 * 
 * Method : AddConvictionHistory Details Return : String Argument :
 * ConvictionHistory c Responsibility: To add Conviction History Details
 * 
 * public int addConviction(ConvictionHistory c) throws SQLException { DBHandler
 * db=new DBHandler(); db.getConnection(); int res=0;
 * System.out.println(c.getDateOfConviction()); String
 * query1="select * from conviction_history where date_of_conviction='"
 * +c.getDateOfConviction()+"' and user_id='"+c.getUserId()+"'";
 * System.out.println(query1); ResultSet rs=db.executeSelect1(query1);
 * if(rs.next()){ System.out.println("details already exist"); } else{ String
 * query
 * ="insert into conviction_history values('"+c.getConvictionHistoryId()+"','"
 * +c.
 * getUserId()+"','"+c.getDateOfConviction()+"','"+c.getNatureOfConviction()+
 * "','" +c.getComments()+"')"; System.out.println(query);
 * res=db.executeDML_Con(query); } db.closeConnection(); return res; }
 * 
 * public String displayAddedConvictionId(String userid,String doc) { String
 * query="select con_id from conviction_history where user_id='"+userid+
 * "' and date_of_conviction='"+doc+"'"; DBHandler db=new DBHandler();
 * db.getConnection(); String conid=db.executeDispConId(query);
 * db.closeConnection(); return conid; }
 * 
 * 
 * Method : display Conviction history id Return : String (conId) Argument :
 * userId Responsibility: To view Conviction History id corresponding to a
 * particular userId
 * 
 * public String getConvictionHistoryId(String userid) throws SQLException {
 * String res = null; String query = "select con_id from conviction_history " +
 * "where user_id='" + userid + "'"; DBHandler db = new DBHandler();
 * db.getConnection(); res = db.executeDispConId(query); db.closeConnection();
 * return res; }
 * 
 * 
 * Method : display Conviction history Details (with the check box to delete the
 * selected convicitonId details) Return : ArrayList<ConvictionHistory> Argument
 * : userId Responsibility: To view Conviction History Details corresponding to
 * a particular userId
 * 
 * public ArrayList<ConvictionHistory> displayConvictionDetails(String userid) {
 * ArrayList<ConvictionHistory> ac = null; try { String query =
 * "select ch.con_id," + "ch.user_id," +
 * "to_char(ch.date_of_conviction,'dd-Mon-yyyy')," +
 * "noc.noc_type as nature_of_conviction," +
 * "ch.comments from conviction_history ch,nature_of_conviction noc" +
 * " where noc.noc_id=ch.nature_of_conviction and user_id='" + userid + "'";
 * DBHandler db = new DBHandler(); db.getConnection(); ac =
 * db.executeViewDML(query); db.closeConnection(); return ac; } catch
 * (SQLException e) { e.printStackTrace(); } return ac; }
 * 
 * 
 * Method : delete Accident History Details Return : integer Argument :String[]
 * convcitionId Responsibility: To delete Conviction History Details
 * corresponding to selected convcitionIds
 * 
 * public int deleteConvictionHistoryDetails(String[] convictionId) throws
 * SQLException { int result = 0; String s = ""; for (int i = 0; i <
 * ((convictionId.length) - 1); i++) { s = s + "'" + convictionId[i] + "', "; }
 * s = s + "'" + convictionId[((convictionId.length) - 1)] + "'"; String query =
 * "delete from conviction_history " + "where con_id in(" + s + ")"; DBHandler
 * db = new DBHandler(); db.getConnection(); result =
 * db.executeDMLDelete(query); db.closeConnection(); return result; }
 * 
 * 
 * Method : display ConvictionHistoryId corresponding to a particular userId
 * Return : ArrayList<String> Argument : userId Responsibility: To view
 * ConvictionHistoryId corresponding to a particular userId
 * 
 * public ArrayList<String> retrieveConId(String userId) { String query =
 * "select con_id from conviction_history " + "where user_id='" + userId + "'";
 * DBHandler db = new DBHandler(); db.getConnection(); ArrayList<String> conId =
 * db.retrieveConvId(query); db.closeConnection(); return conId; }
 * 
 * 
 * Method : view Conviction Details Return : ArrayList<ConvictionHistory>
 * Argument : String userID Responsibility: To view Conviction History Details
 * corresponding to a particular userId
 * 
 * public ArrayList<ConvictionHistory> retrieveConvictionHistory(String userID)
 * {
 * 
 * String query = "select ch.con_id," + "ch.user_id," +
 * "to_char(ch.date_of_conviction,'dd-Mon-yyyy')," +
 * "noc.noc_type as nature_of_conviction," +
 * "ch.comments from conviction_history ch,nature_of_conviction noc" +
 * " where noc.noc_id=ch.nature_of_conviction and user_id='"+userID+"'";
 * DBHandler db = new DBHandler(); db.getConnection();
 * ArrayList<ConvictionHistory> conHist = db.executeDMLQuery(query);
 * db.closeConnection(); return conHist; }
 * 
 * 
 * Method : view Conviction Detail of a particular convictionHistoryId Return :
 * ConvictionHistory Argument : String conHistID Responsibility: To view
 * Conviction History Detail corresponding to a particular convictionhistoryId
 * 
 * public ConvictionHistory retrieveConvictionHistoryOnId(String conID) {
 * 
 * String query = "select ch.con_id," + "ch.user_id," +
 * "to_char(ch.date_of_conviction,'dd-Mon-yyyy')," +
 * "noc.noc_type as nature_of_conviction," +
 * "ch.comments from conviction_history ch,nature_of_conviction noc" +
 * " where noc.noc_id=ch.nature_of_conviction and con_id='"+conID+"'";
 * 
 * DBHandler db = new DBHandler(); db.getConnection(); ConvictionHistory conHist
 * = db.executeDMLQueryOnId(query); db.closeConnection(); return conHist; }
 * 
 * 
 * Method : update Conviction Details (with the radio button to select the
 * details to be updated) Return : integer Argument : String conId, String
 * dateOfConviction, String natureOfConviction, String comments Responsibility:
 * To update Conviction History Details corresponding to a particular
 * convictionHistoryId
 * 
 * public int updateConvictionHistory(String conId, String dateOfConviction,
 * String natureOfConviction, String comments) { String query =
 * "update conviction_history set " + "date_of_conviction='" + dateOfConviction
 * + "'," + "nature_of_conviction='" + natureOfConviction + "'," + "comments='"
 * + comments + "' where con_id='" + conId + "'"; DBHandler db = new
 * DBHandler(); db.getConnection(); int conHistUpdateResult =
 * db.executeDMLUpdate(query); db.closeConnection(); return conHistUpdateResult;
 * }
 * 
 * 
 * 
 * Method : getProposalDetails() Returns : ArrayList of proposal details
 * Arguments : none Responsibility : to return the list of proposals against
 * which a quote has not been generated.
 * 
 * public ArrayList<ProposalDetails> getProposalDetails() {
 * ArrayList<ProposalDetails> pdtls = new ArrayList<ProposalDetails>();
 * ResultSet rs = null; String query = "select * from proposal where flag=0";
 * DBHandler dbc = new DBHandler(); try { dbc.getConnection(); } catch
 * (Exception e2) {
 * 
 * e2.printStackTrace(); } try { rs = dbc.doSelect(query); while (rs.next()) {
 * OpinionThatCountsUser user = new
 * OpinionThatCountsUser(rs.getString("USER_ID")); pdtls.add(new
 * ProposalDetails(rs.getString("PROPOSAL_ID"), user, rs.getString("VEH_ID"),
 * rs.getString("flag"), rs .getDouble("CLAIM_AMOUNT"))); } } catch (Exception
 * e) {
 * 
 * e.printStackTrace(); } return (pdtls); }
 * 
 * 
 * Method : selectProposal() Returns : ProposalDetails Arguments : pid
 * Responsibility : to create and return the object of ProposalDetails asked by
 * the employee
 * 
 * public ProposalDetails selectProposal(String pid) { ProposalDetails pdtls =
 * null; ResultSet rs = null; String query =
 * "select * from proposal where PROPOSAL_ID = '" + pid + "' and flag=0";
 * DBHandler dbc = new DBHandler(); try { dbc.getConnection(); } catch
 * (Exception e2) { e2.printStackTrace(); } try { rs = dbc.doSelect(query);
 * while (rs.next()) { OpinionThatCountsUser user = new
 * OpinionThatCountsUser(rs.getString("USER_ID")); pdtls = new
 * ProposalDetails(rs.getString("PROPOSAL_ID"), user, rs.getString("VEH_ID"),
 * rs.getString("flag"), rs .getDouble("CLAIM_AMOUNT")); } } catch (Exception e)
 * { e.printStackTrace(); } return (pdtls); }
 * 
 * 
 * Method : checkUser() Returns : boolean Arguments : uid Responsibility : to
 * check whether the user is present in the database or not
 * 
 * public boolean isUserExist(String uid) { ResultSet rs = null; String query =
 * "select * from fastquote_user where USER_ID = '" + uid + "'"; DBHandler dbc =
 * new DBHandler(); try { dbc.getConnection(); } catch (Exception e2) {
 * e2.printStackTrace(); }
 * 
 * try { rs = dbc.doSelect(query); if (rs.next()) { return (true); } } catch
 * (Exception e) { e.printStackTrace(); } return false; }
 * 
 * 
 * Method : checkProposal() Returns : boolean Arguments : pid Responsibility :
 * to check whether the proposal is present in the database or not
 * 
 * public boolean checkProposal(String pid) { ResultSet rs = null; String query
 * = "select * from proposal where PROPOSAL_ID = '" + pid + "' "; DBHandler dbc
 * = new DBHandler(); try { dbc.getConnection(); } catch (Exception e2) {
 * 
 * e2.printStackTrace(); } try { rs = dbc.doSelect(query); if (rs.next()) {
 * System.out.println(rs.getString(1)); return (true); } } catch (Exception e) {
 * e.printStackTrace(); } return false; }
 * 
 * 
 * Method : viewQuoteDetails() Returns : ArrayList of QuoteDetails Arguments :
 * uid Responsibility : to get the array list of the all the quotes for one
 * particular user.
 * 
 * public ArrayList<QuoteDetails> viewQuoteDetails(String userId) { ResultSet rs
 * = null; String query = "select p.proposal_id, q.quote_id, q.quote_amount, " +
 * "u.user_name from proposal p, fastquote_user u, " +
 * "quote q where p.proposal_id = q.proposal_id and " + "p.user_id='" + userId +
 * "' and p.user_id=u.user_id"; DBHandler dbc = new DBHandler(); try {
 * dbc.getConnection(); } catch (Exception e2) {
 * 
 * e2.printStackTrace(); } try { rs = dbc.doSelect(query); while (rs.next()) {
 * ProposalDetails pdtls = new ProposalDetails(rs .getString("proposal_id"));
 * qdtls.add(new QuoteDetails(rs.getString("quote_id"), pdtls, rs
 * .getDouble("quote_amount"))); } } catch (Exception e) {
 * 
 * e.printStackTrace(); } return qdtls; }
 * 
 * 
 * Method : getQuoteDetails() Returns : ArrayList of QuoteDetails Arguments :
 * quoteId Responsibility : to get the array list of quote details for one
 * particular quote.
 * 
 * public ArrayList<QuoteDetails> getQuoteDetails(String quoteId) { ResultSet rs
 * = null; String query = "select * from quote where quote_id = '" + quoteId +
 * "'"; DBHandler dbc = new DBHandler(); try { dbc.getConnection(); } catch
 * (Exception e2) { e2.printStackTrace(); } try { rs = dbc.doSelect(query);
 * while (rs.next()) { ProposalDetails pdtls = new ProposalDetails(rs
 * .getString("proposal_id")); qdtls.add(new
 * QuoteDetails(rs.getString("quote_id"), pdtls, rs
 * .getDouble("quote_amount"))); } } catch (Exception e) { e.printStackTrace();
 * } return qdtls; }
 * 
 * 
 * Method : viewQuoteDetailsPid() Returns : ArrayList of QuoteDetails Arguments
 * : proposal id Responsibility : to get the array list of the quotes for given
 * proposal.
 * 
 * public ArrayList<QuoteDetails> viewQuoteDetailsPid(String proposalId) {
 * ResultSet rs = null; String query = "select * from quote q " +
 * "where proposal_id = '" + proposalId + "'"; DBHandler dbc = new DBHandler();
 * try { dbc.getConnection(); } catch (Exception e2) { e2.printStackTrace(); }
 * try { rs = dbc.doSelect(query); while (rs.next()) { ProposalDetails pdtls =
 * new ProposalDetails(rs .getString("proposal_id")); qdtls.add(new
 * QuoteDetails(rs.getString("quote_id"), pdtls, rs
 * .getDouble("quote_amount"))); } } catch (Exception e) { e.printStackTrace();
 * } return qdtls; }
 * 
 * 
 * Method : deleteQuoteDetails() Returns : int Arguments : Array of quote id
 * Responsibility : to delete the quote details for an array of quotes.
 * 
 * public int deleteQuoteDetails(String[] quoteId) { String s = "", pid = "";
 * ResultSet rs; for (int i = 0; i < ((quoteId.length) - 1); i++) { s = s + "'"
 * + quoteId[i] + "', "; } s = s + "'" + quoteId[((quoteId.length) - 1)] + "'";
 * String query = "delete from quote where quote_id in(" + s + ")"; String
 * query1 = "select proposal_id from quote where quote_id " + "in(" + s + ")";
 * DBHandler db = new DBHandler(); db.getConnection(); rs = db.doSelect(query1);
 * try { while (rs.next()) { pid += rs.getString("proposal_id"); //
 * if(rs.isLast()) // break; // pid+=","; } } catch (Exception e) {
 * e.printStackTrace(); } boolean b = db.doDelete(query); if (b) return 0; else
 * { query = "update proposal set flag=0 where proposal_id " + "in ('" + pid +
 * "')"; db.doUpdate(query); return 1; } }
 * 
 * 
 * Method : getProposal() Returns : ProposalDetails Arguments : proposal id
 * Responsibility : to return the proposal details for a particular proposal id.
 * 
 * 
 * public ProposalDetails getProposal(String proposalId) { ResultSet rs = null;
 * String query = "select * from proposal " + "where PROPOSAL_ID = '" +
 * proposalId + "'"; DBHandler dbc = new DBHandler(); ProposalDetails p = null;
 * 
 * try { dbc.getConnection(); } catch (Exception e2) { e2.printStackTrace(); }
 * try { rs = dbc.doSelect(query); while (rs.next()) { OpinionThatCountsUser
 * user = new OpinionThatCountsUser(rs.getString("USER_ID")); p = new
 * ProposalDetails(rs.getString("PROPOSAL_ID"), user, rs .getString("VEH_ID"),
 * rs.getString("FLAG"), rs .getDouble("CLAIM_AMOUNT")); } } catch (Exception e)
 * { e.printStackTrace(); } return p;
 * 
 * }
 * 
 * 
 * Method : generateQuote() Returns : QuoteDetails Arguments : proposal id
 * Responsibility : to quote for a particular proposal with particular
 * prposalId.
 * 
 * public QuoteDetails generateQuote(String proposalId) { ResultSet rs = null;
 * String nat1 = null, quoteId = null; ArrayList<String> nat2 = new
 * ArrayList<String>(); QuoteDetails q = null; double ded = 0, ded1 = 0, ded2 =
 * 0, claim = 0, quote = 0; String query1 =
 * "select nature_of_accident from accident_history a " + "where proposal_id='"
 * + proposalId + "'"; String query2 = "select nature_of_conviction from " +
 * "conviction_history c, proposal p " + "where p.user_id=c.user_id and " +
 * "p.proposal_id='" + proposalId + "'"; DBHandler dbc = new DBHandler();
 * 
 * try { dbc.getConnection();
 * 
 * } catch (Exception e2) { e2.printStackTrace(); } try { rs =
 * dbc.doSelect(query1); while (rs.next()) { nat1 =
 * rs.getString("nature_of_accident"); } if (nat1 == null) {// no accident
 * details available for // the proposal. q = new QuoteDetails("-1");//
 * constructed object just to // pass the error code. return q; } } catch
 * (Exception e) { e.printStackTrace(); }
 * 
 * try { rs = dbc.doSelect(query2); while (rs.next()) {
 * nat2.add(rs.getString("nature_of_conviction")); } } catch (Exception e) {
 * e.printStackTrace(); }
 * 
 * String query3 = "select noa_deduction_rate from nature_of_accident " +
 * "where noa_id='" + nat1 + "'";
 * 
 * try { rs = dbc.doSelect(query3); while (rs.next()) { ded1 = rs.getDouble(1);
 * } } catch (Exception e) { e.printStackTrace(); } for (String val : nat2) {
 * String query4 = "select noc_deduction_rate from " +
 * "nature_of_conviction where noc_id='" + val + "'"; try { rs =
 * dbc.doSelect(query4); while (rs.next()) ded2 +=
 * rs.getDouble("noc_deduction_rate");
 * 
 * }
 * 
 * catch (Exception e) { e.printStackTrace(); }
 * 
 * }
 * 
 * String query5 = "select claim_amount from proposal " + "where proposal_id='"
 * + proposalId + "'"; try { rs = dbc.doSelect(query5); while (rs.next()) {
 * claim = rs.getDouble(1); } } catch (Exception e) { e.printStackTrace(); } ded
 * = ((ded1 + ded2) * claim / 100); quote = claim - ded; if (quote > 0) { String
 * query6 = "select comm_sequence.nextval from dual"; rs = dbc.doSelect(query6);
 * try { while (rs.next()) { quoteId = new Integer(rs.getInt(1)).toString(); } }
 * catch (SQLException e) { e.printStackTrace(); } String query7 =
 * "insert into quote values('QT_" + quoteId + "','" + proposalId + "'," + quote
 * + ")"; ProposalDetails p = new ProposalDetails(proposalId); q = new
 * QuoteDetails("QT_" + quoteId, p, quote); dbc.doInsert(query7);
 * 
 * String query8 = "update proposal set flag=1 " + "where proposal_id='" +
 * proposalId + "'"; dbc.doUpdate(query8); String query9 =
 * "select * from quote " + "where quote_id='QT_||" + quoteId + "'"; rs =
 * dbc.doSelect(query9);
 * 
 * } else { // when total quote amount goes below zero. q = new
 * QuoteDetails("-2");// } return q; }
 * 
 * 
 * Method : checkFlag() Returns : flag(int) of particular proposal. Arguments :
 * proposalId Responsibility : to return the flag(int) of particular
 * proposal,whether the quote for this proposal has been generated or not.
 * 
 * 
 * public int checkFlag(String proposalId) { int i = 0; ResultSet rs = null;
 * String query = "select flag from proposal where PROPOSAL_ID = '" + proposalId
 * + "'"; DBHandler dbc = new DBHandler();
 * 
 * try { dbc.getConnection(); } catch (Exception e2) { e2.printStackTrace(); }
 * try { rs = dbc.doSelect(query); while (rs.next()) { i = rs.getInt("flag"); }
 * } catch (Exception e) { e.printStackTrace(); } return i; }
 * 
 * Method : rejectProposal() Returns : boolean value. Arguments : proposalId
 * Responsibility : to return the flag,whether the proposal has been
 * successfully deleted or not.
 * 
 * 
 * public boolean rejectProposal(String pid) {
 * 
 * int rs=0;
 * 
 * String query = "delete from proposal where PROPOSAL_ID = '" + pid + "'";
 * System.out.println(query); DBHandler dbc = new DBHandler(); try {
 * dbc.getConnection(); } catch (Exception e2) { e2.printStackTrace(); } try {
 * rs = dbc.executeDML(query); } catch (Exception e) { e.printStackTrace(); }
 * query="delete from accident_history where PROPOSAL_ID = '" + pid + "'";
 * System.out.println(query); try { dbc.getConnection(); } catch (Exception e2)
 * { e2.printStackTrace(); } try { dbc.executeDML(query);
 * 
 * } catch (Exception e) { e.printStackTrace(); } if(rs!=0){
 * 
 * return true; } return false; }
 * 
 * public ArrayList<String> retrieveProposalId(String userId) { String query
 * ="select proposal_id from proposal where user_id='"
 * +userId+"' and proposal_id not in (select proposal_id from accident_history)"
 * ; System.out.println(query); DBHandler db = new DBHandler();
 * db.getConnection(); ArrayList<String> proposalId = db.retrieveConvId(query);
 * db.closeConnection(); return proposalId; }
 */

